USE master;
GO

SET NOCOUNT ON;
GO

PRINT 'system manufacturer and model number';
EXECUTE xp_readerrorlog 0, 1, N'Manufacturer';

PRINT 'processor description';
EXECUTE xp_instance_regread 'HKEY_LOCAL_MACHINE',
'HARDWARE\DESCRIPTION\System\CentralProcessor\0','ProcessorNameString';

PRINT 'host information';
SELECT
	host_platform,
	host_distribution,
	host_release, 
    host_service_pack_level,
	host_sku, os_language_version 
FROM sys.dm_os_host_info WITH (NOLOCK) OPTION (RECOMPILE); 

PRINT 'windows power plan settings';
BEGIN TRANSACTION
	DECLARE @value VARCHAR(64);
 
	EXECUTE master..xp_regread 
		@rootkey = 'HKEY_LOCAL_MACHINE',
		@key = 'SYSTEM\CurrentControlSet\Control\Power\User\PowerSchemes',
		@value_name = 'ActivePowerScheme',
		@value = @value OUTPUT;

	SELECT
	CASE @value
		WHEN '381b4222-f694-41f0-9685-ff5bb260df2e'
			THEN 'BALANCED'
		WHEN '8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c'
			THEN 'HIGH PERFORMANCE'
		WHEN 'a1841308-3541-4fab-bc81-f71556f20b4a'
			THEN 'POWER SAVER'
		ELSE 'CUSTOMIZED'
	END
	AS windows_power_plan;
COMMIT TRANSACTION

PRINT 'version info, CPU and scheduler';
SELECT 
	@@SERVERNAME AS server_name,
	LEFT(@@VERSION, CHARINDEX(' - ', @@VERSION,0) - 1) AS sql_server_info,
	-- @@VERSION AS sql_server_and_os_version_info,
	SERVERPROPERTY('Edition') AS edition,
	SERVERPROPERTY('ProductLevel') AS product_level,
	SERVERPROPERTY('ProductVersion') AS product_version,
	@@LANGUAGE AS product_language,
	ISNULL(SERVERPROPERTY('InstanceName'), 'Default') AS instance_name,
	SERVERPROPERTY('ProductUpdateReference') AS product_update_reference,
	SERVERPROPERTY('Collation') AS collation,
	SERVERPROPERTY('IsClustered') AS is_clustered,
	SERVERPROPERTY('IsFullTextInstalled') AS is_fulltext_installed, 
	SERVERPROPERTY('IsIntegratedSecurityOnly') AS is_integratedsecurity_only,
	SERVERPROPERTY('FilestreamConfiguredLevel') AS filestream_configured_level,
	SERVERPROPERTY('IsHadrEnabled') AS is_hadr_enabled, 
	SERVERPROPERTY('HadrManagerStatus') AS hadr_manager_status,
	SERVERPROPERTY('InstanceDefaultDataPath') AS instance_default_datapath,
	SERVERPROPERTY('InstanceDefaultLogPath') AS instance_default_logpath,
	SERVERPROPERTY('BuildClrVersion') AS build_clr_version,
	SERVERPROPERTY('IsXTPSupported') AS is_xtp_supported,
	SERVERPROPERTY('IsPolybaseInstalled') AS is_polybase_installed,				-- >=SQL Server 2016
	SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS is_advanced_analytics_installed,
	cpu_count/hyperthread_ratio AS physical_cpu_count,
	cpu_count AS logical_cpu_count,
	scheduler_count,
	(socket_count * cores_per_socket) AS physical_core_count,
	socket_count AS socket_count, cores_per_socket,
	numa_node_count,
	softnuma_configuration_desc AS softnuma_configuration,
	max_workers_count,
	physical_memory_kb/1024 AS physical_memory_mb,
	sql_memory_model_desc AS sql_memory_model,  -- >=SQL Server 2016 SP1
	affinity_type_desc AS affinity_type,
	virtual_machine_type_desc AS virtual_machine_type, -- NONE | HYPERVISOR | OTHER
	CONVERT(VARCHAR, sqlserver_start_time, 100) AS server_startup_time
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

-- hyperthreading ?
/*
WMI: wmic cpu get numberofcores, numberoflogicalprocessors
CPU-Z_ http://www.cpuid.com/softwares/cpu-z.html
*/

PRINT 'global trace flags';
DBCC TRACESTATUS (-1);

PRINT 'error log';
EXECUTE xp_readerrorlog;

PRINT 'service accounts';
SELECT
	servicename,
	startup_type_desc,
	status_desc,
	last_startup_time,
	service_account,
	filename,
	instant_file_initialization_enabled  -- >=SQL Server 2014 SP2 | SQL Server 2016 SP1
FROM sys.dm_server_services WITH (NOLOCK) OPTION (RECOMPILE);

PRINT 'SQL Server NUMA nodes';
SELECT
	node_id,
	node_state_desc,
	memory_node_id,
	online_scheduler_count, 
    active_worker_count,
	avg_load_balance 
FROM sys.dm_os_nodes WITH (NOLOCK) 
WHERE node_state_desc <> N'ONLINE DAC' OPTION (RECOMPILE);

PRINT 'configuration values for the instance';
SELECT
	name,
	value,
	value_in_use
FROM sys.configurations WITH (NOLOCK)
WHERE name IN (
	'affinity mask',
	'backup compression default',
	'backup checksum default',
	'clr enabled',
	'cost threshold for parallelism',
	'fill factor (%)',
	'lightweight pooling',
	'max degree of parallelism',
	'max server memory (MB)',
	'max worker threads',
	'min server memory (MB)',
	'optimize for ad hoc workloads',
	'priority boost',
	'remote admin connections')
ORDER BY name ASC OPTION (RECOMPILE);

-- >=SQL Server 2012
PRINT 'memory configuration';
SELECT
	osi.physical_memory_kb / 1024 AS physical_memory_mb,
	opm.physical_memory_in_use_kb / 1024 AS physical_memory_in_use_mb,
	osm.available_physical_memory_kb / 1024 AS available_physical_memory_mb,
	opm.locked_page_allocations_kb / 1024 AS locked_page_allocations_mb,
	opm.large_page_allocations_kb / 1024 AS large_page_allocations_mb,
	osi.virtual_memory_kb / 1024 AS virtual_memory_mb,
	osi.committed_kb / 1024 AS bufferpool_committed_mb,
	osi.committed_target_kb / 1024 AS bufferpool_target_mb,
	osi.visible_target_kb / 1024 AS bufferpool_visible_mb,
	opm.available_commit_limit_kb / 1024 AS available_commit_limit_mb, 
	opm.page_fault_count,
	opm.memory_utilization_percentage,
    opm.process_physical_memory_low,
	opm.process_virtual_memory_low,
	osm.system_memory_state_desc,
	max_server_memory_mb,
	min_server_memory_mb
FROM sys.dm_os_sys_info osi WITH (NOLOCK)
CROSS JOIN sys.dm_os_process_memory opm WITH (NOLOCK)
CROSS JOIN sys.dm_os_sys_memory osm WITH (NOLOCK)
CROSS JOIN (
	SELECT value_in_use AS max_server_memory_mb
	FROM sys.configurations WITH (NOLOCK)
	WHERE [name] = 'max server memory (MB)') AS c1
CROSS JOIN (
	SELECT value_in_use AS min_server_memory_mb
	FROM sys.configurations WITH (NOLOCK)
	WHERE [name] = 'min server memory (MB)') AS c2 OPTION (RECOMPILE);

PRINT 'memory clerk usage for instance';
SELECT TOP (10)
	type AS memory_clerk_type,
	CAST((SUM(pages_kb) / 1024.0) AS decimal(18,2)) AS pages_mb
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY type
ORDER BY SUM(pages_kb) DESC;

PRINT 'memory allocated to the memory clerks';
-- memory allocated to the buffer pool
SELECT
    'SQLBUFFERPOOL' AS memory_clerk_type,
	SUM(pages_kb 
        + virtual_memory_committed_kb
        + shared_memory_committed_kb
        + awe_allocated_kb) / 1024 AS pages_mb
FROM sys.dm_os_memory_clerks  WITH (NOLOCK)
WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL'
UNION
-- memory allocated to sql excluding the buffer pool
SELECT
	'NON_SQLBUFFERPOOL' AS memory_clerk_type,
    SUM(pages_kb 
        + virtual_memory_committed_kb
        + shared_memory_committed_kb) / 1024 AS pages_mb
FROM sys.dm_os_memory_clerks  WITH (NOLOCK)
WHERE type <> 'MEMORYCLERK_SQLBUFFERPOOL' OPTION (RECOMPILE);

PRINT 'buffer pool usage per database';
SELECT
	DB_NAME(database_id) AS database_name,
    CAST(COUNT(*) * 8 / 1024.0 AS decimal(18,2)) AS cached_size_mb
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id > 4 -- system databases
      AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY cached_size_mb DESC OPTION (RECOMPILE);

PRINT 'ring buffer cpu resource messages';
SELECT 
    record.value('(/Record/@id)[1]', 'int') AS ID,
	event_time,
    n.value('(SystemIdle)[1]', 'int') AS idle_cpu_percent,
		100-(n.value('(SystemIdle)[1]', 'int') +
			n.value('(ProcessUtilization)[1]', 'int')) AS nonsql_cpu_percent,
    n.value('(ProcessUtilization)[1]', 'int') AS sql_cpu_percent
FROM (
	SELECT
		DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS event_time, 
		CONVERT (xml, record) AS record
	FROM sys.dm_os_ring_buffers WITH (NOLOCK)
	CROSS JOIN sys.dm_os_sys_info WITH (NOLOCK)
	WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR') AS t
CROSS APPLY record.nodes('/Record/SchedulerMonitorEvent/SystemHealth') AS x(n)
ORDER BY ID DESC OPTION (RECOMPILE);

PRINT 'ring buffer memory resource messages';
-- 1 High Physical Memory | 2 Low Physical Memory | 4 Low Virtual Memory
SELECT
    record.value('(/Record/@id)[1]', 'int') AS ID,
	event_time, 
    record.value('(/Record/ResourceMonitor/Notification)[1]', 'varchar(max)') AS event_type,
	record.value('(/Record/ResourceMonitor/IndicatorsProcess)[1]', 'int') AS indicators_process,
    record.value('(/Record/ResourceMonitor/IndicatorsSystem)[1]', 'int') AS indicators_system,
	record.value('(//Record/MemoryNode/@id)[1]', 'bigint') AS node_id,
	CAST(record.value('(/Record/MemoryNode/TargetMemory)[1]', 'bigint') / 1024.0 AS decimal(18,2)) AS sql_target_memory_mb,
    CAST(record.value('(/Record/MemoryNode/CommittedMemory)[1]', 'bigint') / 1024.0 AS decimal(18,2)) AS sql_committed_memory_mb,
	CAST(record.value('(/Record/MemoryNode/AWEMemory)[1]', 'bigint') / 1024.0 AS decimal(18,2)) AS sql_awe_memory_mb,
	record.value('(/Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS memory_utilization_percent,
	CAST(record.value('(/Record/MemoryRecord/TotalPhysicalMemory)[1]', 'bigint') / 1024.0 AS decimal(18,2)) AS total_physical_memory_mb,
	CAST(record.value('(/Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'bigint') / 1024.0 AS decimal(18,2)) AS available_physical_memory_mb, 
    CAST(record.value('(/Record/MemoryRecord/AvailableVirtualAddressSpace)[1]', 'bigint') / 1024.0 AS decimal(18,2)) AS available_virtual_address_space_mb
FROM ( 
    SELECT 
        DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS event_time, 
        CONVERT (xml, record) AS record 
    FROM sys.dm_os_ring_buffers WITH (NOLOCK) 
    CROSS JOIN sys.dm_os_sys_info WITH (NOLOCK) 
    WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') AS tab 
ORDER BY ID DESC OPTION (RECOMPILE);

PRINT 'average wait times critical I/O paths';
SELECT
	'WRITELOG' AS wait_type,
	wait_time_ms/waiting_tasks_count AS avg_wait_time_ms
FROM sys.dm_os_wait_stats WITH (NOLOCK)
WHERE wait_type = 'WRITELOG'
UNION SELECT
	'PAGEIOLATCH' AS wait_type,
	SUM(wait_time_ms)/SUM(waiting_tasks_count) AS avg_wait_time_ms
FROM sys.dm_os_wait_stats WITH (NOLOCK)
WHERE wait_type LIKE 'PAGEIOLATCH%' OPTION (RECOMPILE);

PRINT 'traces';
SELECT * FROM sys.traces WITH (NOLOCK) OPTION (RECOMPILE);

PRINT 'extended event session';
SELECT * FROM sys.dm_xe_sessions WITH (NOLOCK) OPTION (RECOMPILE);

PRINT 'SQL connections by IP address';
SELECT
	ec.client_net_address,
	es.[program_name],
	es.[host_name],
	es.login_name, 
	COUNT(ec.session_id) AS connection_count 
FROM sys.dm_exec_sessions es WITH (NOLOCK)
INNER JOIN sys.dm_exec_connections ec WITH (NOLOCK)
	ON es.session_id = ec.session_id 
GROUP BY ec.client_net_address, es.[program_name], es.[host_name], es.login_name  
ORDER BY ec.client_net_address, es.[program_name] OPTION (RECOMPILE);

PRINT 'resource governor resource pool information';
SELECT
	pool_id,
	[name] AS pool_name,
	statistics_start_time,
    min_memory_percent, max_memory_percent,  
    max_memory_kb/1024 AS max_memory_mb,  
    used_memory_kb/1024 AS used_memory_mb,   
    target_memory_kb/1024 AS target_memory_mb,
	min_iops_per_volume, max_iops_per_volume
FROM sys.dm_resource_governor_resource_pools WITH (NOLOCK)
OPTION (RECOMPILE);

PRINT 'AlwaysOn AG cluster information';
SELECT
	cluster_name,
	quorum_type_desc,
	quorum_state_desc
FROM sys.dm_hadr_cluster WITH (NOLOCK) OPTION (RECOMPILE);

PRINT 'overview of AG health and status';
SELECT
	ag.name AS availability_group_name,
	ar.replica_server_name, ar.availability_mode_desc, adc.database_name, 
    drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant, 
	drs.synchronization_health_desc
	--drs.recovery_lsn, drs.truncation_lsn, drs.last_sent_lsn, 
	--drs.last_sent_time, drs.last_received_lsn, drs.last_received_time, drs.last_hardened_lsn, 
	--drs.last_hardened_time, drs.last_redone_lsn, drs.last_redone_time, drs.log_send_queue_size, 
	--drs.log_send_rate, drs.redo_queue_size, drs.redo_rate, drs.filestream_send_rate, 
	--drs.end_of_log_lsn, drs.last_commit_lsn, drs.last_commit_time, drs.database_state_desc 
FROM sys.dm_hadr_database_replica_states AS drs WITH (NOLOCK)
INNER JOIN sys.availability_databases_cluster AS adc WITH (NOLOCK)
	ON drs.group_id = adc.group_id 
AND drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag WITH (NOLOCK)
	ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar WITH (NOLOCK)
	ON drs.group_id = ar.group_id 
AND drs.replica_id = ar.replica_id
ORDER BY ag.name ASC, ar.replica_server_name ASC, adc.database_name ASC OPTION (RECOMPILE);

PRINT 'suspect pages';
SELECT
	DB_NAME(database_id) AS database_name,
	[file_id], page_id, 
    event_type, error_count, last_update_date 
FROM msdb.dbo.suspect_pages WITH (NOLOCK)
ORDER BY database_id ASC OPTION (RECOMPILE);

PRINT 'memory dumps from SQL Server';
SELECT
	[filename] AS 'file_name',
	creation_time,
	size_in_bytes/(POWER(1024.0, 2)) AS size_mb
FROM sys.dm_server_memory_dumps WITH (NOLOCK) 
ORDER BY creation_time DESC OPTION (RECOMPILE);

PRINT 'SQL Server Agent alert information';
SELECT
	name AS alert_name,
	event_source,
	message_id,
	severity,
	[enabled] AS is_enabled,
	has_notification, 
    delay_between_responses,
	occurrence_count, last_occurrence_date, last_occurrence_time
FROM msdb.dbo.sysalerts WITH (NOLOCK)
ORDER BY name ASC OPTION (RECOMPILE);

PRINT 'job history';
SELECT
	-- jb.job_id,
	-- jb.enabled AS job_enabled,
	jb.name AS job_name,
	-- jb.description As job_description,
	-- jb.date_created,
	-- ct.name AS category_name,
	jsd.next_run_date,
	STUFF(STUFF(RIGHT('000000' + CAST(jsd.next_run_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		AS next_run_time,
	CASE jsv.last_run_outcome
		WHEN 0 THEN 'Fail'
		WHEN 1 THEN 'Succeed'
		WHEN 2 THEN 'Cancel'
	END AS last_outcome_status,
	jsv.last_outcome_message,
	jsv.last_run_date,
	STUFF(STUFF(RIGHT('000000' + CAST(jsv.last_run_time AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		AS last_run_time,
	STUFF(STUFF(RIGHT('000000' + CAST(jsv.last_run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
		AS last_run_duration,
	-- sd.name AS schedule_name,
	-- sd.enabled AS schedule_enabled,
	CASE sd.freq_type
		WHEN 1 THEN 'One time only'
		WHEN 4 THEN 'Daily'
		WHEN 8 THEN 'Weekly'
		WHEN 16 THEN 'Monthly'
		WHEN 32 THEN 'Monthly Relative' 
		WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
		WHEN 128 THEN 'Runs when the computer is idle'
	END AS frequency_type
FROM msdb.dbo.sysjobs jb WITH (NOLOCK)
-- INNER JOIN msdb.dbo.syscategories ct ON jb.category_id = ct.category_id
LEFT OUTER JOIN msdb.dbo.sysjobservers jsv WITH (NOLOCK) ON jb.job_id = jsv.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules jsd WITH (NOLOCK) ON jsd.job_id = jb.job_id
LEFT OUTER JOIN msdb.dbo.sysschedules sd WITH (NOLOCK) ON jsd.schedule_id = sd.schedule_id
WHERE jb.enabled = 1 OPTION (RECOMPILE);

PRINT 'file names and paths for tempdb and all user databases';
SELECT
	DB_NAME(mf.database_id) AS database_name,
    mf.[file_id],
	mf.name AS logical_name,
	mf.physical_name,
	mf.type_desc,
	mf.state_desc,
	CEILING(mf.size * 8 / 1024.0) AS file_size_mb,
	CASE mf.is_percent_growth
		WHEN 1 THEN 'PERCENT'
		WHEN 0 THEN 'MB'
		ELSE 'UNKNOWN'
	END AS growth_type,
	CASE WHEN is_percent_growth = 1 THEN CAST(growth AS varchar(10))  + '%' 
	ELSE
		CAST(ROUND(growth * 8 /1024,0) AS varchar(20)) 
	END AS growth_value,
	CASE WHEN mf.max_size = -1 OR mf.max_size = 268435456 THEN 'UNLIMITED'
		WHEN mf.max_size = 0 THEN 'NO_GROWTH' 
		WHEN mf.max_size <> -1 OR mf.max_size <> 0 THEN CAST(((mf.max_size * 8) / 1024) AS varchar(15))
		ELSE 'UNKNOWN'
	END AS maxfile_size_mb,
	vs.volume_mount_point,
	vs.total_bytes / (POWER(1024, 3)) AS total_gb,
	vs.available_bytes / (POWER(1024, 3)) AS available_gb,
	CAST(CAST(vs.available_bytes AS FLOAT)/ CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS space_free_percent
FROM sys.master_files mf WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) vs
WHERE mf.database_id > 4 
AND mf.database_id <> 32767
OR mf.database_id = 2
ORDER BY DB_NAME(mf.database_id) ASC OPTION (RECOMPILE);

PRINT 'configuration and log file information for all databases';
SELECT
	db.[name] AS database_name,
	db.recovery_model_desc,
	db.log_reuse_wait_desc, 
	ls.cntr_value AS log_size_kb,
	lu.cntr_value AS log_used_kb,
	CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)AS DECIMAL(18,2)) * 100 AS log_used_percent,
	db.[compatibility_level],
	db.page_verify_option_desc,
	db.containment_desc,
	db.is_auto_create_stats_on,
	db.is_auto_update_stats_on,
	db.is_auto_update_stats_async_on,
	db.is_parameterization_forced,
	db.snapshot_isolation_state AS is_snapshot_isolation_state_on,
	db.is_read_committed_snapshot_on,
	db.is_read_only,
	db.is_encrypted,
	db.is_auto_close_on,
	db.is_auto_shrink_on,
	db.delayed_durability_desc,
	db.target_recovery_time_in_seconds,
	db.is_query_store_on,
	db.collation_name,
	db.is_fulltext_enabled
FROM sys.databases AS db
INNER JOIN sys.dm_os_performance_counters AS lu
ON db.name = lu.instance_name
INNER JOIN sys.dm_os_performance_counters AS ls 
ON db.name = ls.instance_name
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%' 
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0
ORDER BY DB_NAME(db.database_id) ASC OPTION (RECOMPILE);

PRINT 'VLF counts for all databases on the instance';
-- >= SQL Server 2016 SP2
SELECT
	db.name AS database_name,
	db.database_id,
	COUNT(li.database_id) AS 'vlf_count',
	SUM(li.vlf_size_mb) AS 'vlf_size_mb',
	SUM(CAST(li.vlf_active AS INT)) AS 'active_vlf_count',
	SUM(li.vlf_active*li.vlf_size_mb) AS 'active_vlf_size_mb',
	COUNT(li.database_id)-SUM(CAST(li.vlf_active AS INT)) AS 'inactive_vlf_count',
	SUM(li.vlf_size_mb)-SUM(li.vlf_active*vlf_size_mb) AS 'inactive_vlf_size_mb'
FROM sys.databases db WITH (NOLOCK)
CROSS APPLY sys.dm_db_log_info(db.database_id) li
GROUP BY db.[name], db.database_id
HAVING COUNT(li.database_id) > 20
ORDER BY 'vlf_count' DESC OPTION (RECOMPILE);

-- >=SQL Server 2012
/*
CREATE TABLE #VLFInfo
	(RecoveryUnitID int, FileID int,
	FileSize bigint, StartOffset bigint,
	FSeqNo bigint, [Status] bigint,
	Parity bigint, CreateLSN numeric(38));
	 
CREATE TABLE #VLFCount
	(DatabaseName sysname, VLFCount int);

EXECUTE sp_MSforeachdb N'USE [?]; 
	INSERT INTO #VLFInfo 
	EXECUTE sp_executesql N''DBCC LOGINFO([?]) WITH NO_INFOMSGS''; 
	 
	INSERT INTO #VLFCount
	SELECT DB_NAME(), COUNT(*) 
	FROM #VLFInfo; 

	TRUNCATE TABLE #VLFInfo;'
	 
SELECT
	DatabaseName AS database_name,
	VLFCount AS vlf_count 
FROM #VLFCount
ORDER BY VLFCount DESC;
	 
DROP TABLE #VLFInfo;
DROP TABLE #VLFCount;
*/

PRINT 'I/O statistics and I/O stalls for all databases';
;WITH DatabaseIOStats AS
(
  SELECT
    DB_NAME(fs.database_id) AS database_name,
    mf.type_desc,
    SUM(fs.num_of_bytes_read + fs.num_of_bytes_written) AS io_bytes,
    SUM(fs.io_stall) AS io_stall_ms
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) fs
    INNER JOIN sys.master_files mf WITH (NOLOCK)
      ON fs.database_id = mf.database_id
      AND fs.file_id = mf.file_id
  GROUP BY fs.database_id, mf.type_desc
)
SELECT
	ROW_NUMBER() OVER(ORDER BY io_stall_ms DESC) AS row_num,
	database_name,
	type_desc, 
	CAST(1. * io_bytes / (1024 * 1024) AS DECIMAL(12, 2)) AS io_mb,
    CAST(io_stall_ms / 1000.0 AS DECIMAL(12, 2)) AS io_stall_s,
    CAST(100.0 * io_stall_ms / SUM(io_stall_ms) OVER()
       AS DECIMAL(10, 2)) AS io_stall_percent
FROM DatabaseIOStats
ORDER BY row_num ASC OPTION (RECOMPILE);

PRINT 'CPU statistics for all databases';
;WITH DatabaseCPUStats AS
(
  SELECT
    pa.database_id,
	DB_NAME(pa.database_id) AS database_name,
	CAST((SUM(qs.total_worker_time) / 1000.0) AS DECIMAL(12,0)) AS cpu_time_ms
  FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
  CROSS APPLY (SELECT CONVERT(int, value) AS database_id 
	FROM sys.dm_exec_plan_attributes(qs.plan_handle)
    WHERE attribute = N'dbid') AS pa
  GROUP BY pa.database_id)
SELECT
	ROW_NUMBER() OVER(ORDER BY cpu_time_ms DESC) AS row_num,
    database_name,
	cpu_time_ms, 
	CAST(cpu_time_ms * 1.0 / SUM(cpu_time_ms) OVER() * 100.0 AS DECIMAL(5, 2)) AS cpu_time_percent
FROM DatabaseCPUStats
WHERE database_id > 4	  -- system databases
AND database_id <> 32767  -- ResourceDB
ORDER BY row_num ASC OPTION (RECOMPILE);

PRINT 'database last full | log backup history';
;WITH DatabaseBackups AS
(
SELECT
	DENSE_RANK() OVER(PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC) AS [ranking],
	bs.server_name,
	bs.database_name, 
	bs.recovery_model,
	CASE bs.[type]
		WHEN 'D' THEN 'Database'
		WHEN 'I' THEN 'Differential database'
		WHEN 'L' THEN 'Log'
		WHEN 'F' THEN 'File or filegroup'
		WHEN 'G' THEN 'Differential file'
		WHEN 'P' THEN 'Partial'
		WHEN 'Q' THEN 'Differential partial'
		ELSE '-'
	END AS backup_type,
	bmf.media_set_id,
	bmf.family_sequence_number,
	bs.backup_start_date,
	bs.backup_finish_date,
    DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) AS backup_elapsed_time_s,
	CAST((bs.backup_size / POWER(1024.0, 2)) AS DECIMAL(18, 2)) AS backup_size_mb,
	CAST((bs.compressed_backup_size / POWER(1024.0, 2)) AS DECIMAL(18, 2)) AS compressed_backup_size_mb,
	CAST((CAST(bs.backup_size AS FLOAT) /
		CAST(bs.compressed_backup_size AS FLOAT)) AS DECIMAL(18,2)) AS compression_ratio,
	bs.has_backup_checksums, bs.is_copy_only, bs.encryptor_type,
	bmf.physical_device_name AS backup_location, bmf.physical_block_size
FROM msdb.dbo.backupset bs WITH (NOLOCK)
INNER JOIN msdb.dbo.backupmediafamily AS bmf WITH (NOLOCK)
	ON bs.media_set_id = bmf.media_set_id  
WHERE DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0 
AND bs.backup_size > 0
)
SELECT * FROM DatabaseBackups WHERE [ranking] = 1;

PRINT 'database file growth';
DECLARE @tracefilename VARCHAR(260);
SELECT @tracefilename = LEFT([path], LEN([path]) - PATINDEX('%\%', REVERSE([path]))) + '\log.trc' FROM sys.traces WHERE is_default = 1;

;WITH AutoGrow (databaseid, [filename], growth, duration, start_time, end_time)
AS
(
	SELECT
		databaseid,
		[filename],
		SUM(integerdata * 8) AS growth,
		duration,
		starttime, endtime
	FROM ::fn_trace_gettable(@tracefilename, default)
	WHERE EventClass >= 92 AND EventClass <= 95 AND DATEDIFF(hh, starttime, GETDATE()) < 72 -- last 72h
	GROUP BY databaseid, [filename], integerdata, duration, starttime, endtime
)
SELECT
	DB_NAME(database_id) AS database_name, 
	mf.name AS logical_name,
	mf.physical_name,
	mf.type_desc,
	mf.size * 8 / 1024 AS size_mb,
	(ag.growth * 8) /1024 AS growth_mb, CASE WHEN is_percent_growth = 1 THEN 'PCT' ELSE 'MB' END AS growth_type,
	ag.duration/1000 AS growth_duration_ms,
	ag.start_time, ag.end_time
FROM sys.master_files mf WITH (NOLOCK)
LEFT OUTER JOIN AutoGrow ag ON mf.database_id = ag.databaseid AND mf.name = ag.[filename]
WHERE ag.Growth > 0
GROUP BY database_id, mf.name, mf.physical_name, mf.type_desc, mf.size, ag.growth, ag.duration, ag.start_time, ag.end_time, is_percent_growth, mf.growth
ORDER BY database_name ASC, logical_name ASC, ag.start_time ASC OPTION (RECOMPILE);